# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import  mysql.connector
from scrapy.exceptions import DropItem

class RemoveNullPipeline:
    def process_item(self, item, spider):
        for field, value in item.items():
            if value is None or value == '':
                raise DropItem(f"Item contains null value in field:{field}")
        return item

class DoubanPipeline:
    def process_item(self, item, spider):
        return item


class MySQLDoubanPipeline(object):
    def open_spider(self, spider):
        self.connection = mysql.connector.connect(
            host=spider.settings.get('MYSQL_HOST'),
            database=spider.settings.get('MYSQL_DATABASE'),
            user = spider.settings.get('MYSQL_USER'),
            password = spider.settings.get('MYSQL_PASSWORD'),
            port = spider.settings.get('MYSQL_PORT'),
        )
        self.cursor = self.connection.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.connection.close()

    def process_item(self, item, spider):
        if item['kind'] == 'DouBan':
            self.cursor.execute(
                "INSERT INTO comment (comment_name,comment_link,comment_content,movie_name,movie_link,movie_rate,movie_director,movie_type,movie_time,movie_country,movie_language) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)",
                (item.get('comment_name'),item.get('comment_link'),item.get('comment_content'),item.get('movie_name'),item.get('movie_link'),item.get('movie_rate'),item.get('movie_director'),item.get('movie_type'),item.get('movie_time'),item.get('movie_country'),item.get('movie_language'))
            )
            self.connection.commit()
        return item

class MySQLMtimePipeline(object):
    def open_spider(self, spider):
        self.connection = mysql.connector.connect(
            host=spider.settings.get('MYSQL_HOST'),
            database=spider.settings.get('MYSQL_DATABASE'),
            user = spider.settings.get('MYSQL_USER'),
            password = spider.settings.get('MYSQL_PASSWORD'),
            port = spider.settings.get('MYSQL_PORT'),
        )
        self.cursor = self.connection.cursor()

    def close_spider(self, spider):
        self.cursor.close()
        self.connection.close()

    def process_item(self, item, spider):
        for field, value in item.items():
            if value is None or value == '':
                raise DropItem(f"Item contains null value in field:{field}")
        if item['kind'] == 'Mtime':
            self.cursor.execute(
                "INSERT INTO comment_mtime (comment_name,comment_content,movie_name,movie_time,movie_rate,movie_type,movie_country) VALUES (%s,%s,%s,%s,%s,%s,%s)",
                (item.get('comment_name'),item.get('comment_content'),item.get('movie_name'),item.get('movie_time'),item.get('movie_rate'),item.get('movie_type'),item.get('movie_country'))            )
            self.connection.commit()
        return item